package com.ss.web.tagmark.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.ss.web.tagmark.entity.TagInfo;
import com.ss.web.tagmark.entity.User;

/**
 * 数据库方面的操作
 *
 * @author taofucheng
 *
 */
public interface UserDao extends BaseRepository<User, Long> {

	default Page<User> findByPage(User user, Pageable page, EntityManager em) {
		String sql = "SELECT * FROM  `user` u WHERE 1=1 ";
		Map<String, Object> params = new HashMap<>();
		if (StringUtils.isNotBlank(user.getFullname())) {
			sql += " and (MATCH(`fullname`) AGAINST (CONCAT('*',:fullname,'*') IN BOOLEAN MODE) OR fullname_py like concat(:fullname,'%') OR fullname_py_short like concat(:fullname,'%'))";
			params.put("fullname", user.getFullname());
		}
		if (StringUtils.isNotBlank(user.getLogname())) {
			sql += " and logname like concat(:logname,'%')";
			params.put("logname", user.getLogname());
		}
		if (StringUtils.isNotBlank(user.getStatus())) {
			sql += " and status = :status";
			params.put("status", user.getStatus());
		}
		if (user.getTags() != null && user.getTags().size() > 0) {
			List<Long> tagIds = new ArrayList<>();
			sql += " AND u.`user_id` IN (SELECT ut.user_id FROM tag_info t LEFT JOIN user_tag ut ON ut.`tag_id`=t.`tag_id` WHERE t.`tag_id` IN (:tagId))";
			for (TagInfo tag : user.getTags()) {
				if (tag == null || tag.getTagId() == null) {
					continue;
				}
				tagIds.add(tag.getTagId());
			}
			params.put("tagId", tagIds);
		}
		return doQuery(page, sql, User.class, params, em);
	}

	@Query(nativeQuery = true, value = "SELECT * FROM `user` u WHERE ( MATCH(`fullname`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE) "// 姓名
			+ " OR MATCH(`fullname_py`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE) "// 拼音
			+ " OR MATCH(`fullname_py_short`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE) "// 拼音首字母
			+ " OR u.`logname` LIKE CONCAT(:q, '%')"// 状态和登录名
			+ " OR u.`user_id` IN (SELECT ut.user_id FROM tag_info t INNER JOIN user_tag ut ON ut.`tag_id`=t.`tag_id` WHERE MATCH(t.`name`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE)"
			+ " OR MATCH(`name_py`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE) "// 拼音
			+ " OR MATCH(`name_py_short`) AGAINST (CONCAT('*', :q, '*') IN BOOLEAN MODE) "// 拼音首字母
			+ ")" + ") AND u.`status` = '1' limit 50")
	public List<User> find4Search(@Param("q") String q);

	public boolean existsByLognameAndUserIdNot(String logname, Long userId);

	public boolean existsByLogname(String logname);

	@Query(nativeQuery = true, value = "SELECT * FROM `user` u WHERE u.user_id IN (SELECT oper_id FROM annotation a WHERE a.`article_id`=:articleId)")
	public List<User> selectByArticleId(@Param("articleId") Long articleId);

	public User findByLogname(String logname);
}
